﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Castle.ActiveRecord;
using NHibernate.Criterion;
using NHibernate.Engine;
using System.Data.SqlClient;
using ZDSoft.UPMS.Model.BusinessPart;
using NHibernate;
using ZDSoft.UPMS.Model;
using System.Data;

namespace ZDSoft.UPMS.Manager
{
    public class ManagerBase<T> : ActiveRecordBase
      where T : class
    {
        /// <summary>
        /// 创建一个实体
        /// </summary>
        /// <param name="t">实体</param>
        public void Create(T t)
        {
            ActiveRecordBase.Create(t);
        }

        /// <summary>
        /// 修改一个实体
        /// </summary>
        /// <param name="t">实体</param>
        public void Update(T t)
        {
            ActiveRecordBase.Update(t);
        }

        /// <summary>
        /// 删除一个实体
        /// </summary>
        /// <param name="id">实体ID</param>
        public void Delete(int id)
        {
            ActiveRecordBase.Delete(FindByID(id));
        }

        /// <summary>
        /// 查找一个实体
        /// </summary>
        /// <param name="id">实体ID</param>
        /// <returns>实体</returns>
        public T FindByID(int id)
        {
            return (T)ActiveRecordBase.FindByPrimaryKey(typeof(T), id);
        }

        /// <summary>
        /// 查找所有实体
        /// </summary>
        /// <returns>实体集合</returns>
        public IList<T> FindAll()
        {
            return ActiveRecordBase.FindAll(typeof(T)) as IList<T>;
        }

        /// <summary>
        /// 根据查询条件获取所有满足条件的实体
        /// </summary>
        /// <param name="queryConditions">查询条件，可以是多个,形式如："UserName"="Lion"</param>
        /// <returns>满足条件的实体集合</returns>
        public IList<T> Find(IList<ICriterion> queryConditions)
        {
            Array arr = ActiveRecordBase.FindAll(typeof(T), queryConditions.ToArray());
            return arr as IList<T>;
        }

        /// <summary>
        /// 分页获取对象集合
        /// </summary>
        /// <param name="queryConditions">查询条件，可以是多个,形式如："UserName"="Michael"</param>
        /// <param name="orderList">排序方式，根据字段排序，形式如："UserName","DESC"</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="count">满足条件的记录总数</param>
        /// <returns>返回满足条件的实体集合</returns>
        public IList<T> GetPaged(IList<ICriterion> queryConditions, IList<Order> orderList, int pageIndex,
            int pageSize, out int count)
        {
            if (queryConditions == null)//如果为null则赋值为一个总数为0的集合
            {
                queryConditions = new List<ICriterion>();//为了满足ActiveRecordBase.SlicedFindAll方法，即查询条件不能为null
            }
            if (orderList == null)//如果为null则赋值为一个总数为0的集合
            {
                orderList = new List<Order>();//为了满足ActiveRecordBase.SlicedFindAll方法，即排序条件不能为null
            }
            count = ActiveRecordBase.Count(typeof(T), queryConditions.ToArray());//根据查询条件获取满足条件的对象总数
            Array arr = ActiveRecordBase.SlicedFindAll(typeof(T), (pageIndex - 1) * pageSize, pageSize, orderList.ToArray(), queryConditions.ToArray());//根据查询条件分页获取对象集合
            return arr as IList<T>;
        }


        /// <summary>
        /// 根据查询条件分页获取实体
        /// </summary>
        /// <param name="queryConditions">查询条件集合</param>
        /// <param name="pageIndex">当前页码，从1开始</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="count">返回满足查询条件</param>
        /// <returns>返回满足查询条件的实体</returns>
        public IList<T> GetPaged(IList<QueryConditions> queryConditions, int pageIndex, int pageSize, out int count)
        {
            //实例化一个hql查询语句对象
            StringBuilder hql = new StringBuilder(@"from " + typeof(T).Name + " d");
            //根据查询条件构造hql查询语句
            for (int i = 0; i < queryConditions.Count; i++)
            {
                QueryConditions qc = queryConditions[i];//获取当前序号对应的条件
                if (qc.Value != null)
                {
                    AddHqlSatements(hql);//增加where或and语句
                    hql.Append(string.Format("d.{0} {1} :q_{2}", qc.PropertyName, qc.Operator, i));
                }
            }

            ISession session = ActiveRecordBase.holder.CreateSession(typeof(T));//获取管理T的session对象
            IQuery query = session.CreateQuery(hql.ToString());//获取满足条件的数据
            IQuery queryScalar = session.CreateQuery("select count(ID) " + hql.ToString());//获取满足条件的数据的总数
            for (int i = 0; i < queryConditions.Count; i++)
            {
                QueryConditions qc = queryConditions[i];//获取当前序号对应的条件
                if (qc.Value != null)
                {
                    //如果是like语句，则修改值的表达方式
                    if (qc.Operator.ToUpper() == "LIKE")
                    {
                        qc.Value = "%" + qc.Value + "%";
                    }

                    //用查询条件的值去填充hql，如d.Transportor.Name="michael"
                    queryScalar.SetParameter("q_" + i, qc.Value);
                    query.SetParameter("q_" + i, qc.Value);
                }
            }

            IList<object> result = queryScalar.List<object>();//执行查询条件总数的查询对象，返回一个集合（有一点怪异）
            int.TryParse(result[0].ToString(), out count);//尝试将返回值的第一个值转换为整形，并将转换成功的值赋值给count，如果转换失败,count=0
            query.SetFirstResult((pageIndex - 1) * pageSize);//设置获取满足条件实体的起点
            query.SetMaxResults(pageSize);//设置获取满足条件实体的终点
            IList<T> arr = query.List<T>();//返回当前页的数据
            return arr;
        }

        /// <summary>
        /// 根据查询条件分页获取实体
        /// </summary>
        /// <param name="queryConditions">查询条件集合</param>
        /// <returns>返回满足查询条件的实体</returns>
        public IList<T> Find(IList<QueryConditions> queryConditions)
        {
            //实例化一个hql查询语句对象
            StringBuilder hql = new StringBuilder(@"from " + typeof(T).Name + " d");
            //根据查询条件构造hql查询语句
            for (int i = 0; i < queryConditions.Count; i++)
            {
                QueryConditions qc = queryConditions[i];//获取当前序号对应的条件
                if (qc.Value != null)
                {
                    AddHqlSatements(hql);//增加where或and语句
                    hql.Append(string.Format("d.{0} {1} :q_{2}", qc.PropertyName, qc.Operator, i));
                }
            }

            ISession session = ActiveRecordBase.holder.CreateSession(typeof(T));//获取管理T的session对象
            IQuery query = session.CreateQuery(hql.ToString());//获取满足条件的数据
            IQuery queryScalar = session.CreateQuery("select count(ID) " + hql.ToString());//获取满足条件的数据的总数
            for (int i = 0; i < queryConditions.Count; i++)
            {
                QueryConditions qc = queryConditions[i];//获取当前序号对应的条件
                if (qc.Value != null)
                {
                    //如果是like语句，则修改值的表达方式
                    if (qc.Operator.ToUpper() == "LIKE")
                    {
                        qc.Value = "%" + qc.Value + "%";
                    }

                    //用查询条件的值去填充hql，如d.Transportor.Name="michael"
                    queryScalar.SetParameter("q_" + i, qc.Value);
                    query.SetParameter("q_" + i, qc.Value);
                }
            }

            IList<T> arr = query.List<T>();//返回当前页的数据
            return arr;
        }

        protected void AddHqlSatements(StringBuilder hql)
        {
            if (!hql.ToString().Contains("where"))//查询语句的开始条件是where
            {
                hql.Append(" where ");
            }
            else//当hql中有了一个where后再添加查询条件时就应该使用and了
            {
                hql.Append(" and ");
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="str"></param>
        public void Exec(string str)
        {
            //Castle.Net调用存储过程
            //获得当前ORM与数据库之间的联结对象
            NHibernate.Cfg.Configuration[] cfg = ActiveRecordBase.holder.GetAllConfigurations();
            //构建一个Session的实例
            ISessionFactoryImplementor factory = (ISessionFactoryImplementor)cfg[0].BuildSessionFactory();
            SqlConnection conn = (SqlConnection)factory.ConnectionProvider.GetConnection();//将ORM的联结对象转换成SqlConnection
            if (conn.State != System.Data.ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(str, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        /// <summary>
        /// 判断表或者函数是否存在
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public bool IsHaveFunction(string Name)
        {
            string strsql = "select COUNT(*) from sys.objects where name='" + Name + "'";
            object obj = GetSingle(strsql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            NHibernate.Cfg.Configuration[] cfg = ActiveRecordBase.holder.GetAllConfigurations();
            ISessionFactoryImplementor factory = (ISessionFactoryImplementor)cfg[0].BuildSessionFactory();
            using (SqlConnection connection = (SqlConnection)factory.ConnectionProvider.GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        if (connection.State != System.Data.ConnectionState.Open)
                        {
                            connection.Open();
                        }
                        object obj = cmd.ExecuteScalar();
                        connection.Close();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="procedure"></param>
        /// <param name="parameter"></param>
        public void ExeNonQuery(string procedure, SqlParameter[] parameter)
        {
            NHibernate.Cfg.Configuration[] cfg = ActiveRecordBase.holder.GetAllConfigurations();
            ISessionFactoryImplementor factory = (ISessionFactoryImplementor)cfg[0].BuildSessionFactory();
            SqlConnection conn = (SqlConnection)factory.ConnectionProvider.GetConnection();//将ORM的联结对象转换成SqlConnection
            SqlCommand com = BuildQueryCommand(procedure, parameter);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            com.Connection = conn;
            com.ExecuteNonQuery();
            conn.Close();
        }
        private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
        {
            //根据提供的名字创建Sql命令
            SqlCommand command = new SqlCommand(storedProcName);
            //指定命令的类型为存储过程
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput ||
                        parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    //将参数添加到SQL命令中
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }
        /// <summary>
        /// 创建存储过程参数字段。
        /// </summary>
        /// <param name="SqlParameterNames">参数名</param>
        /// <param name="SqlValues">参数值</param>
        /// <returns>存储过程参数列表</returns>
        public SqlParameter[] CreateSqlParameter(List<string> SqlParameterNames, List<Object> SqlValues)
        {
            List<SqlParameter> SqlParameterList = new List<SqlParameter>();

            for (int i = 0; i < SqlParameterNames.Count; i++)
            {
                SqlParameter sp = new SqlParameter(SqlParameterNames[i], SqlValues[i]);
                SqlParameterList.Add(sp);
            }

            return SqlParameterList.ToArray();
        }
    }
}
